/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.util.ArrayList;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import pojo.Foreignkey;
import pojo.Property;
import pojo.QueryInfo;
import pojo.Row;
import pojo.Table;

/**
 *
 * @author HoangLiem
 */
public class ExecRowXML {
    /*
     * Hàm này lấy tất cả các dòng trong table của 1 database.
     */

    public static ArrayList<Row> getRowsOfTable(Document doc, String tableName) {
        tableName = tableName.toLowerCase();
        ArrayList<Row> rows = new ArrayList<Row>();
        try {
            doc.getDocumentElement().normalize();
            String xPath = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']/row";

            NodeList nodeList = (NodeList) ExecXML.execXpath(doc, xPath, "nodeset");
            Table table = ExecTableXML.getTableFromDatabase(doc, tableName);
            if (nodeList != null && nodeList.getLength() > 0) {
                for (int i = 0; i < nodeList.getLength(); i++) {
                    Node node = nodeList.item(i);
                    if (node.getNodeType() == Node.ELEMENT_NODE) {
                        if (node.hasAttributes()) {
                            //voi moi nut id, ta duyet cac List ID, list property trong table
                            // roi lấy gia trị trong row theo ten cac id, property
                            Element ele = (Element) node;
                            Row row = new Row();
                            if (!table.getIdList().isEmpty()) {
                                for (int j = 0; j < table.getIdList().size(); j++) {
                                    Property id = table.getIdList().get(j);
                                    row.setValueString(ele.getAttribute(id.getName()));
                                }
                            }
                            if (!table.getPropertyList().isEmpty()) {
                                for (int j = 0; j < table.getPropertyList().size(); j++) {
                                    Property property = table.getPropertyList().get(j);
                                    row.setValueString(ele.getAttribute(property.getName()));
                                }
                            }
                            rows.add(row);
                        }
                    }
                }
            }
            if (rows.isEmpty()) {
                rows = null;
            }
        } catch (Exception e) {
            rows = null;
            e.printStackTrace();
        }
        return rows;
    }

    public static ArrayList<Row> getRowsOfTable(String databaseName, String tableName) {
        databaseName = databaseName.trim().toLowerCase();
        tableName = tableName.trim().toLowerCase();
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return getRowsOfTable(doc, tableName);
        } catch (Exception ex) {
            return null;
        }
    }
    /*
     *  Hàm này lấy hết các giá trị ID của 1 bảng,
     *  nếu id có nhiều thuộc tính, ta công nó lại làm 1 chuỗi
     */

    public static ArrayList<String> getIdsDataOfTable(Document doc, String tableName) {
        ArrayList<String> idsData = new ArrayList<String>();
        try {
            ArrayList<Row> rows = getRowsOfTable(doc, tableName);
            Table table = ExecTableXML.getTableFromDatabase(doc, tableName);
            int numberId = 0;
            if (table.getIdList() != null) {
                numberId = table.getIdList().size();
            }
            table.getIdList().size();
            if (rows != null) {
                for (int i = 0; i < rows.size(); i++) {
                    Row row = rows.get(i);
                    String idData = "";
                    if (numberId > 0) {
                        for (int j = 0; j < numberId; j++) {
                            idData = idData + row.getData().get(j);
                        }
                    }
                    idsData.add(idData);
                }
            }
            if (idsData.isEmpty()) {
                idsData = null;
            }
        } catch (Exception e) {
            idsData = null;
            e.printStackTrace();
        }
        return idsData;
    }

    public static ArrayList<String> getIdsDataOfTable(String databaseName, String tableName) {
        databaseName = databaseName.trim().toLowerCase();
        tableName = tableName.trim().toLowerCase();
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return getIdsDataOfTable(doc, tableName);
        } catch (Exception ex) {
            return null;
        }
    }
    /*
     * Hàm này kiểm tra giá trị ID của 1 row
     * xem có trùng với các giá trị id khác trong table không.
     */

    public static boolean isExistedIdData(Document doc, String tableName, Row row) {
        boolean existed = false;
        try {
            ArrayList<String> idsData = getIdsDataOfTable(doc, tableName);
            Table table = ExecTableXML.getTableFromDatabase(doc, tableName);
            ArrayList<Property> ids = new ArrayList<Property>();
            if (table != null) {
                ids = table.getIdList();
                String idData = "";
                if (ids != null && !ids.isEmpty()) {
                    for (int i = 0; i < ids.size(); i++) {
                        idData = idData + row.getData().get(i);
                    }
                }
                if (!idData.equals("")) {
                    existed = ExecXML.isExistedString(idsData, idData);
                }
            } else {
                existed = true;
            }
        } catch (Exception ex) {
            existed = true;
        }
        return existed;
    }

    public static boolean isExistedIdData(String databaseName, String tableName, Row row) {
        databaseName = databaseName.trim().toLowerCase();
        tableName = tableName.trim().toLowerCase();
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return isExistedIdData(doc, tableName, row);
        } catch (Exception ex) {
            return true;
        }
    }
    /*
     * Ham này kiem tra gia tri khoa ngoai trong 1 row co ton tai trong table ref không
     */

    public static boolean isExistedForeignKeyData(Document doc, String tableName, Row row) {
        boolean existed = true;
        try {
            ArrayList<Foreignkey> fks = ExecForeignkeyXML.getForeignkeyOfTable(doc, tableName);
            if (fks == null || fks.isEmpty()) {
                return existed = true;
            }

            for (int i = 0; i < fks.size(); i++) {
                Foreignkey fk = fks.get(i);
                String[] proRefs = fk.getPropertyref().split(",");
                String[] idRefs = fk.getIdref().split(",");
                String tableRef = fk.getTableref();
                String fkData = "";
                for (int j = 0; j < proRefs.length; j++) {
                    fkData = fkData + row.getData().get(ExecPropertyXML.getActualPosition(doc, tableName, proRefs[j]));
                }
                ArrayList<String> idsDataTableRef = getIdsDataOfTable(doc, tableRef);
                if (!ExecXML.isExistedString(idsDataTableRef, fkData)) {
                    return existed = false;
                }
            }
        } catch (Exception ex) {
            existed = false;
        }
        return existed;
    }

    public static boolean isExistedForeignKeyData(String databaseName, String tableName, Row row) {
        databaseName = databaseName.trim().toLowerCase();
        tableName = tableName.trim().toLowerCase();
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return isExistedForeignKeyData(doc, tableName, row);
        } catch (Exception ex) {
            return false;
        }
    }
    /*
     * Hàm này kiểm tra dữ liệu nhập vào 1 table có hợp lệ không
     */

    public static boolean isValidatedData(Document doc, String tableName, Row row) {
        boolean result = true;
        try {
            Table table = ExecTableXML.getTableFromDatabase(doc, tableName);
            if (table != null) {
                ArrayList<Property> ids = table.getIdList();
                ArrayList<Property> properties = table.getPropertyList();

                // <editor-fold defaultstate="collapsed" desc="Phần ID">
                if (ids != null && !ids.isEmpty()) {
                    for (int i = 0; i < ids.size(); i++) {
                        Property id = ids.get(i);
                        String type = id.getType();
                        int idPos = ExecPropertyXML.getActualPosition(doc, tableName, id.getName());
                        String value = "";
                        if (idPos > -1) {
                            value = row.getData().get(idPos);
                            if (type.equalsIgnoreCase("String")) {
                                //Xu ly cho string
                            } else {
                                if (type.equalsIgnoreCase("integer") || type.equalsIgnoreCase("int")) {
                                    if (!ExecXML.isInteger(value)) {
                                        return false;
                                    }
                                } else {
                                    if (!ExecXML.isReal(value)) {
                                        return false;
                                    }
                                }
                            }
                        } else {
                            return result = false;
                        }
                    }
                }
                // </editor-fold> 

                // <editor-fold defaultstate="collapsed" desc="Phần property">
                if (properties != null && !properties.isEmpty()) {
                    for (int i = 0; i < properties.size(); i++) {
                        Property pro = properties.get(i);
                        String type = pro.getType();
                        int proPos = ExecPropertyXML.getActualPosition(doc, tableName, pro.getName());
                        String value = "";
                        if (proPos > -1) {
                            value = row.getData().get(proPos);
                            if (type.equalsIgnoreCase("String")) {
                                //Xu ly cho string
                            } else {
                                if (type.equalsIgnoreCase("integer") || type.equalsIgnoreCase("int")) {
                                    if (!ExecXML.isInteger(value)) {
                                        return false;
                                    }
                                } else {
                                    if (!ExecXML.isReal(value)) {
                                        return false;
                                    }
                                }
                            }
                        } else {
                            return result = false;
                        }
                    }
                }
                // </editor-fold> 
            } else {
                result = false;
            }
        } catch (Exception ex) {
            result = false;
        }
        return result;
    }

    public static boolean isValidatedData(String databaseName, String tableName, Row row) {
        databaseName = databaseName.trim().toLowerCase();
        tableName = tableName.trim().toLowerCase();
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return isValidatedData(doc, tableName, row);
        } catch (Exception ex) {
            return false;
        }
    }
    /*
     * Hàm này kiểm tra dữ liệu có not null
     */

    public static boolean isNotNullData(Document doc, String tableName, Row row) {
        boolean result = true;
        try {
            Table table = ExecTableXML.getTableFromDatabase(doc, tableName);
            if (table != null) {
                ArrayList<Property> ids = table.getIdList();
                ArrayList<Property> properties = table.getPropertyList();

                // <editor-fold defaultstate="collapsed" desc="Phần ID">
                if (ids != null && !ids.isEmpty()) {
                    for (int i = 0; i < ids.size(); i++) {
                        Property id = ids.get(i);
                        boolean notnull = id.isNotnull();
                        int idPos = ExecPropertyXML.getActualPosition(doc, tableName, id.getName());
                        String value = "";
                        if (idPos > -1) {
                            value = row.getData().get(idPos);
                            if (notnull) {
                                if (value.equals("") || value == null) {
                                    return result = false;
                                }
                            }
                        } else {
                            return result = false;
                        }
                    }
                }
                // </editor-fold> 

                // <editor-fold defaultstate="collapsed" desc="Phần property">
                if (properties != null && !properties.isEmpty()) {
                    for (int i = 0; i < properties.size(); i++) {
                        Property pro = properties.get(i);
                        boolean notnull = pro.isNotnull();
                        int proPos = ExecPropertyXML.getActualPosition(doc, tableName, pro.getName());
                        String value = "";
                        if (proPos > -1) {
                            value = row.getData().get(proPos);
                            if (notnull) {
                                if (value.equals("") || value == null) {
                                    return result = false;
                                }
                            }
                        } else {
                            return result = false;
                        }
                    }
                }
                // </editor-fold> 
            } else {
                result = false;
            }
        } catch (Exception ex) {
            result = false;
        }
        return result;
    }

    public static boolean isNotNullData(String databaseName, String tableName, Row row) {
        databaseName = databaseName.trim().toLowerCase();
        tableName = tableName.trim().toLowerCase();
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return isNotNullData(doc, tableName, row);
        } catch (Exception ex) {
            return false;
        }
    }
    /*
     *  Hàm này lấy hết các giá trị dữ liệu của 1 property trong 1 bảng,
     */

    public static ArrayList<String> getPropertiesDataOfTable(Document doc, String tableName, String proName) {
        ArrayList<String> propertiesData = new ArrayList<String>();
        try {
            ArrayList<Row> rows = getRowsOfTable(doc, tableName);
            Table table = ExecTableXML.getTableFromDatabase(doc, tableName);
            if (table != null && rows != null && !rows.isEmpty()) {
                int proPos = ExecPropertyXML.getActualPosition(doc, tableName, proName);
                if (proPos > -1) {
                    for (int i = 0; i < rows.size(); i++) {
                        Row row = rows.get(i);
                        String proRowValue = row.getData().get(proPos);
                        propertiesData.add(proRowValue);
                    }
                }
            }
            if (propertiesData.isEmpty()) {
                propertiesData = null;
            }
        } catch (Exception e) {
            propertiesData = null;
            e.printStackTrace();
        }
        return propertiesData;
    }

    public static ArrayList<String> getPropertiesDataOfTable(String databaseName, String tableName, String proName) {
        databaseName = databaseName.trim().toLowerCase();
        tableName = tableName.trim().toLowerCase();
        proName = proName.trim().toLowerCase();
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return getPropertiesDataOfTable(doc, tableName, proName);
        } catch (Exception ex) {
            return null;
        }
    }
    /*
     * Ham này kiểm tra giá trị là duy nhất
     */

    public static boolean isUniqueData(Document doc, String tableName, Row row) {
        boolean result = true;
        try {
            Table table = ExecTableXML.getTableFromDatabase(doc, tableName);
            if (table != null) {
                ArrayList<Property> ids = table.getIdList();
                ArrayList<Property> properties = table.getPropertyList();

                // <editor-fold defaultstate="collapsed" desc="Phần ID">
                if (ids != null && !ids.isEmpty()) {
                    for (int i = 0; i < ids.size(); i++) {
                        Property id = ids.get(i);
                        boolean unique = id.isUnique();
                        int idPos = ExecPropertyXML.getActualPosition(doc, tableName, id.getName());
                        String value = "";
                        ArrayList<String> idDataList = getPropertiesDataOfTable(doc, tableName, id.getName());
                        if (idPos > -1) {
                            value = row.getData().get(idPos);
                            if (unique) {
                                if (ExecXML.isExistedString(idDataList, value)) {
                                    return result = false;
                                }
                            }
                        } else {
                            return result = false;
                        }
                    }
                }
                // </editor-fold> 

                // <editor-fold defaultstate="collapsed" desc="Phần property">
                if (properties != null && !properties.isEmpty()) {
                    for (int i = 0; i < properties.size(); i++) {
                        Property pro = properties.get(i);
                        boolean unique = pro.isUnique();
                        int proPos = ExecPropertyXML.getActualPosition(doc, tableName, pro.getName());
                        String value = "";
                        ArrayList<String> proDataList = getPropertiesDataOfTable(doc, tableName, pro.getName());
                        if (proPos > -1) {
                            value = row.getData().get(proPos);
                            if (unique) {
                                if (ExecXML.isExistedString(proDataList, value)) {
                                    return result = false;
                                }
                            }
                        } else {
                            return result = false;
                        }
                    }
                }
                // </editor-fold> 
            } else {
                result = false;
            }
        } catch (Exception ex) {
            result = false;
        }
        return result;
    }

    public static boolean isUniqueData(String databaseName, String tableName, Row row) {
        databaseName = databaseName.trim().toLowerCase();
        tableName = tableName.trim().toLowerCase();
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return isUniqueData(doc, tableName, row);
        } catch (Exception ex) {
            return false;
        }
    }
    /*
     * Hàm này thêm 1 row vào table bất kỳ 
     */

    public static boolean insertRow(String databaseName, String tableName, Row row) {
        boolean result = true;
        if (ExecRowXML.isExistedIdData(databaseName, tableName, row)) {
            return false;
        }
        if (!ExecRowXML.isExistedForeignKeyData(databaseName, tableName, row)) {
            return false;
        }
        if (!ExecRowXML.isValidatedData(databaseName, tableName, row)) {
            return false;
        }
        if (!ExecRowXML.isNotNullData(databaseName, tableName, row)) {
            return false;
        }
        if (!ExecRowXML.isUniqueData(databaseName, tableName, row)) {
            return false;
        }
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            if (isExistedIdData(doc, tableName, row)) {
                return result = false;
            }
            doc.getDocumentElement().normalize();
            NodeList nl = doc.getElementsByTagName("table");
            Node node = null;
            Element tb = null;
            for (int i = 0; i < nl.getLength(); i++) {
                node = nl.item(i);
                if (node.getNodeType() == Node.ELEMENT_NODE) {
                    tb = (Element) node;
                    Table table = new Table();
                    if (tb.getAttribute("name").equals(tableName)) {
                        table = ExecTableXML.getTableFromDatabase(doc, tableName);
                        Element r = doc.createElement("row");
                        int n = 0;
                        if (!table.getIdList().isEmpty()) {
                            for (int j = 0; j < table.getIdList().size(); j++) {
                                r.setAttribute(table.getIdList().get(j).getName(), row.getData().get(j));
                                n++;
                            }
                        }
                        if (!table.getPropertyList().isEmpty()) {
                            for (int j = 0; j < table.getPropertyList().size(); j++) {
                                r.setAttribute(table.getPropertyList().get(j).getName(), row.getData().get(n + j));
                            }

                        }
                        tb.appendChild(doc.createTextNode("\t"));
                        tb.appendChild(r);
                        tb.appendChild(doc.createTextNode("\n\t"));
                    }
                }
            }
            ExecXML.saveXmlFile(doc, databaseName + ".xml");
        } catch (Exception e) {
            result = false;
            e.printStackTrace();
        }
        return result;
    }
    /*
     * Hàm xóa row theo dieu kien cua cac gia tri thuoc tinh,
     * co the xoa nhieu row tuy vao dieu kien xoa
     * tra ve so dong dc xoa
     */

    public static int deleteRow(String databaseName, String tableName, QueryInfo queryInfo) {
        int result = 0;
        if (queryInfo == null) {
            return result;
        }
        ArrayList<String> propertyNames = queryInfo.getPropertyNames();
        ArrayList<String> propertyData = queryInfo.getPropertyData();
        databaseName = databaseName.toLowerCase().trim();
        tableName = tableName.toLowerCase().trim();
        if (propertyNames == null || propertyNames.isEmpty() || propertyData == null || propertyData.isEmpty()
                || propertyNames.size() != propertyData.size()) {
            return result;
        }
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            String xPathTable = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']";
            String xPathRow = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']/row";
            Node table = (Node) ExecXML.execXpathReturnNode(doc, xPathTable);
            NodeList rowList = (NodeList) ExecXML.execXpathReturnNodeList(doc, xPathRow);
            if (table != null && rowList != null) {
                if (rowList.getLength() > 0) {
                    for (int i = 0; i < rowList.getLength(); i++) {
                        Node row = rowList.item(i);
                        if (row.getNodeType() == Node.ELEMENT_NODE) {
                            Element element = (Element) row;
                            boolean canDelete = true;
                            for (int j = 0; j < propertyNames.size(); j++) {
                                String value = element.getAttribute(propertyNames.get(j).toLowerCase().trim());
                                String rowData = propertyData.get(j);
                                if (!value.equals(rowData)) {
                                    canDelete = false;
                                    break;
                                }
                            }
                            if (canDelete) {
                                //truoc khi xoa ta can set null cho het cac gia tri o khoa ngoai
                                //lay 1 node o truoc, thuong la 1 text node, xoa luon text node
                                Node textNodeFirst = row.getPreviousSibling();
                                table.removeChild(row);
                                if (textNodeFirst.getNodeType() == Node.TEXT_NODE) {
                                    table.removeChild(textNodeFirst);
                                }
                                boolean saved = ExecXML.saveXmlFile(doc, databaseName + ".xml");
                                if (saved) {
                                    result = result + 1;
                                }
                            }
                        }
                    }
                }
            } else {
                result = 0;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }
    /*
     * Hàm update row theo dieu kien cua cac gia tri thuoc tinh(queryCondition),
     * co the update nhieu row tuy vao dieu kien
     * tra ve so dong dc update
     * 
     */

    public static int updateRow(String databaseName, String tableName, QueryInfo queryCondition, QueryInfo updateData) {
        int result = 0;
        if (queryCondition == null || updateData == null) {
            return result;
        }
        ArrayList<String> conditionPropertyNames = queryCondition.getPropertyNames();
        ArrayList<String> conditionPropertyData = queryCondition.getPropertyData();
        databaseName = databaseName.toLowerCase().trim();
        tableName = tableName.toLowerCase().trim();
        if (conditionPropertyNames == null || conditionPropertyNames.isEmpty()
                || conditionPropertyData == null || conditionPropertyData.isEmpty()
                || conditionPropertyNames.size() != conditionPropertyData.size()) {
            return result;
        }
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            String xPathRow = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']/row";
            NodeList rowList = (NodeList) ExecXML.execXpathReturnNodeList(doc, xPathRow);
            if (rowList != null && rowList.getLength() > 0) {
                for (int i = 0; i < rowList.getLength(); i++) {
                    Node row = rowList.item(i);
                    if (row.getNodeType() == Node.ELEMENT_NODE) {
                        Element element = (Element) row;
                        boolean canUpdate = true;
                        for (int j = 0; j < conditionPropertyNames.size(); j++) {
                            String value = element.getAttribute(conditionPropertyNames.get(j).toLowerCase().trim());
                            String rowData = conditionPropertyData.get(j);
                            if (!value.equals(rowData)) {//kiem tra dieu kien , neu co 1 gia dieu kien ko thoa thi ko update
                                canUpdate = false;
                                break;
                            }
                        }
                        if (canUpdate) {
                            //duyet thong tin can update
                            ArrayList<String> updatePropertyNames = updateData.getPropertyNames();
                            ArrayList<String> updatePropertyData = updateData.getPropertyData();
                            if (updatePropertyNames == null || updatePropertyNames.isEmpty()
                                    || updatePropertyData == null || updatePropertyData.isEmpty()
                                    || updatePropertyNames.size() != updatePropertyData.size()) {
                                return result;
                            }
                            int length = updatePropertyNames.size();
                            for (int j = 0; j < length; j++) {
                                String updatePropertyName = updatePropertyNames.get(j).toLowerCase().trim();
                                String updatePropertyValue = updatePropertyData.get(j);
                                element.setAttribute(updatePropertyName, updatePropertyValue);
                            }
                            boolean saved = ExecXML.saveXmlFile(doc, databaseName + ".xml");
                            if (saved) {
                                result = result + 1;
                            }
                        }
                    }
                }
            } else {
                result = 0;
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    /*
     * Hàm này xóa 1 thuộc tính của các row trong table
     */
    public static boolean deleteAttributeOfRow(Document doc, String tableName, String attributeName) {
        boolean result = true;
        try {
            doc.getDocumentElement().normalize();
            String xPath = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']/row";
            NodeList nodeList = (NodeList) ExecXML.execXpath(doc, xPath, "nodeset");
            for (int i = 0; i < nodeList.getLength(); i++) {
                Node node = nodeList.item(i);
                Element row = (Element) node;
                row.removeAttribute(attributeName);
            }
            //ExecXML.saveXmlFile(doc, ExecTableXML.getNameDatabase(doc)+".xml");
        } catch (Exception e) {
            result = false;
            e.printStackTrace();
        }
        return result;
    }

    /*
     * Hàm này xóa 1 thuộc tính của các row trong table
     */
    public static boolean insertAttributeOfRow(Document doc, String tableName, String attributeName) {
        boolean result = true;
        try {
            doc.getDocumentElement().normalize();
            String xPath = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']/row";
            NodeList nodeList = (NodeList) ExecXML.execXpath(doc, xPath, "nodeset");
            Property pt = ExecPropertyXML.getPropertyOfTable(doc, tableName, attributeName);

            for (int i = 0; i < nodeList.getLength(); i++) {
                Node node = nodeList.item(i);
                Element row = (Element) node;

                if (!pt.isNotnull() && !pt.isUnique()) {
                    row.setAttribute(attributeName, "");
                }
                if (pt.isNotnull() && !pt.isUnique()) {
                    row.setAttribute(attributeName, "0");
                }
                if (!pt.isNotnull() && pt.isUnique()) {
                    row.setAttribute(attributeName, Integer.toString(i));
                }
                if (pt.isNotnull() && pt.isUnique()) {
                    row.setAttribute(attributeName, Integer.toString(i));
                }
            }
            //ExecXML.saveXmlFile(doc, ExecTableXML.getNameDatabase(doc)+".xml");
        } catch (Exception e) {
            result = false;
            e.printStackTrace();
        }
        return result;
    }

    /*
     * hàm này kiem tra xem, có một tồn tại giá trị khóa ngoại ở table khác 
     * bằng gia tri ID trong dong truyen vào hay không
     */
    public static boolean isExistedFkValue(String databaseName, String tableName, Element row) {
        boolean result = false;
        try {
            databaseName = databaseName.toLowerCase().trim();
            tableName = tableName.toLowerCase().trim();
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            ArrayList<Table> tableList = ExecTableXML.getTablesFromDatabase(doc);

            for (int i = 0; i < tableList.size(); i++) {
                Table table = tableList.get(i);
                String curTableName = table.getName().toLowerCase().trim();
                if (table.getForeignKeyList() != null) {
                    String xPathFK = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + curTableName + "']/foreignkey[translate(@tableref,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName + "']";
                    NodeList fkNodeList = (NodeList) ExecXML.execXpathReturnNodeList(doc, xPathFK);
                    ArrayList<Foreignkey> fkList = new ArrayList<Foreignkey>();
                    if (fkNodeList != null && fkNodeList.getLength() > 0) {
                        for (int j = 0; j < fkNodeList.getLength(); j++) {
                            Node fkNode = fkNodeList.item(j);
                            Element fkElement = (Element) fkNode;
                            Foreignkey fk = new Foreignkey();
                            fk.setPropertyref(fkElement.getAttribute("propertyref"));
                            fk.setTableref(fkElement.getAttribute("tableref"));
                            fk.setIdref(fkElement.getAttribute("idref"));
                            fkList.add(fk);
                        }
                    }
                    if (fkList != null && !fkList.isEmpty()) {
                        for (int j = 0; j < fkList.size(); j++) {
                            Foreignkey fk = fkList.get(j);
                            if (fk.getTableref().equalsIgnoreCase(tableName)) {
                                String propertyref = fk.getPropertyref();
                                String idref = fk.getIdref();
                                String xPath = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + table.getName().toLowerCase() + "']/row";
                                NodeList rowList = (NodeList) ExecXML.execXpath(doc, xPath, "nodeset");
                                for (int k = 0; k < rowList.getLength(); k++) {
                                    Node rowNode = rowList.item(k);
                                    Element er = (Element) rowNode;
                                    if (er.getAttribute(propertyref).equalsIgnoreCase(row.getAttribute(idref))) {
                                        return result = true;
                                    }
                                }
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            result = false;
            e.printStackTrace();
        }
        return result;

    }
}
